package com.sto.stoapp.db;

import java.io.File;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;

import com.sto.stoapp.dataprocess.ProcessAftermarket;
import com.sto.stoapp.db.constant.DataDictConstant;
import com.sto.stoapp.db.constant.EffectiveTypeConstant;
import com.sto.stoapp.db.constant.ErrorRecordConstant;
import com.sto.stoapp.db.constant.FrequencyConstant;
import com.sto.stoapp.db.constant.GoodsTypeConstant;
import com.sto.stoapp.db.constant.OrgRuleConstant;
import com.sto.stoapp.db.constant.RegionInfoConstant;
import com.sto.stoapp.db.constant.ScanRuleConstant;
import com.sto.stoapp.db.constant.StoBaseConstant;
import com.sto.stoapp.db.constant.StoBillcodeConstant;
import com.sto.stoapp.db.constant.StoCastSonstant;
import com.sto.stoapp.db.constant.StoKailiAftermarketConstant;
import com.sto.stoapp.db.constant.StoLoginInfoConstant;
import com.sto.stoapp.db.constant.StoOrderConstant;
import com.sto.stoapp.db.constant.StoOrderReasonConstant;
import com.sto.stoapp.db.constant.StoProblemConstant;
import com.sto.stoapp.db.constant.StoRouteConstant;
import com.sto.stoapp.db.constant.StoSalesConstant;
import com.sto.stoapp.db.constant.StoServerSmsConstant;
import com.sto.stoapp.db.constant.StoSiteCodeConstant;
import com.sto.stoapp.db.constant.SystemConstant;
import com.sto.stoapp.db.constant.UserConstant;
import com.sto.stoapp.db.constant.UserInfoConstant;
import com.sto.stoapp.db.constant.UserRuleConstant;
import com.sto.stoapp.status.EnumTableNames;
import com.sto.stoapp.status.OperateEnumTable;
import com.sto.stoapp.util.UnifiedStaticType;

/**
 * 创建数据库类
 * 
 * @author Administrator
 * 
 */
public class StoDataBase extends SQLiteOpenHelper implements
		StoLoginInfoConstant, StoProblemConstant, StoSiteCodeConstant,
		StoSalesConstant, StoServerSmsConstant, StoOrderConstant,
		StoOrderReasonConstant, StoKailiAftermarketConstant, StoRouteConstant,
		StoCastSonstant, StoBillcodeConstant, UserInfoConstant,
		UserRuleConstant, DataDictConstant, FrequencyConstant,
		GoodsTypeConstant, OrgRuleConstant, ScanRuleConstant, SystemConstant,
		RegionInfoConstant, EffectiveTypeConstant, UserConstant,
		ErrorRecordConstant {

	private SQLiteDatabase writeDB = null;
	private SQLiteDatabase readDB = null;
	StoTableFieldName fieldName = new StoTableFieldName();
	StoTableName stoTableName = new StoTableName();
	public static final String PATH = Environment.getExternalStorageDirectory()
			+ File.separator + "sto" + File.separator;

	Context context;

	public StoDataBase(Context context) {
		super(context, UnifiedStaticType.DataBase_Name, null,
				UnifiedStaticType.DATABASE_VERSION); // PATH +
		this.context = context;
	}

	public String getMaxVersionSql(String tableName) {
		return "select Max(" + StoBaseConstant.VERSION + ") as version from "
				+ tableName;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		System.out.println("StoDataBase:" + "onCreate.");
		CreateTable(db);
		CreateSignInfoTable(db);
		createKailiAftermarketTable(db);

		db.execSQL(CREATE_LOGIN_INFO_TABLE);
		db.execSQL(CREATE_PROBLEM_TABLE);
		db.execSQL(CREATE_ORDER_REASON_TABLE);
		db.execSQL(CREATE_SITE_FULL_TABLE);
		db.execSQL(CREATE_NEXT_SITE_TABLE);
		db.execSQL(CREATE_SALES_TABLE);
		db.execSQL(CREATE_SERVER_SMS_TABLE);
		db.execSQL(CREATE_ORDER_TABLE);
		db.execSQL(CREATE_ROUTE_TABLE);
		db.execSQL(CREATE_FORE_CAST_TABLE);
		db.execSQL(CREATE_BILLCODE_TABLE);
		// createIndex(db);
		db.execSQL(CREATE_USER_TABLE);
		db.execSQL(CREATE_USER_INFO_TABLE);
		db.execSQL(CREATE_USER_RULE_TABLE);
		db.execSQL(CREATE_DATA_DICT_TABLE);
		db.execSQL(CREATE_FREQUENCY_TABLE);
		db.execSQL(CREATE_GOODS_TYPE_TABLE);
		db.execSQL(CREATE_ORG_RULE_TABLE);
		db.execSQL(CREATE_SCAN_RULE_TABLE);
		db.execSQL(CREATE_SYSTEM_SET_TABLE);
		db.execSQL(CREATE_REGION_INFO_TABLE);
		db.execSQL(CREATE_EFFECTIVE_TYPE_TABLE);
		db.execSQL(CREATE_ERROR_RECORD_TABLE);

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		// if (oldVersion == 1) {
		// createIndex(db);
		// }
		// if (oldVersion < 3) {
		// db.execSQL(CREATE_BILLCODE_TABLE);
		// }
		// if (oldVersion < 4 && newVersion == 4) {
		// alterTable(db);
		// }
		if (oldVersion < 12 && oldVersion != 10) {
			for (EnumTableNames tableName : EnumTableNames.values()) {
				dropTable(db, tableName.getTableName());
			}

			dropTable(db, new StoSingInfoTable().SIGN_INFO_TABLE_NAME);
			dropTable(db, LOGIN_INFO_TABLE);
			dropTable(db, PROBLEM_TABLE);
			dropTable(db, ORDER_REASON_TABLE);
			dropTable(db, SITE_CODE_FULL_TABLE);
			dropTable(db, NEXT_SITE_CODE_TABLE);
			dropTable(db, SALES_TABLE);
			dropTable(db, SERVER_SMS_DOWNLOAD_TABLE);
			dropTable(db, ORDER_DOWNLOAD_TABLE);
			dropTable(db, TAB_ROUTE);
			dropTable(db, TAB_FORE_CAST);
			dropTable(db, BILLCODE_TABLE);
			dropTable(db, USER_TABLE);
			dropTable(db, USER_INFO_TABLE);
			dropTable(db, USER_RULE_TABLE);
			dropTable(db, DATA_DICT_TABLE);
			dropTable(db, FREQUENCY_TABLE);
			dropTable(db, GOODS_TYPE_TABLE);
			dropTable(db, ORG_RULE_TABLE);
			dropTable(db, SCAN_RULE_TABLE);
			dropTable(db, SYSTEM_SET_TABLE);
			dropTable(db, REGION_INFO_TABLE);
			dropTable(db, EFFECTIVE_TYPE_TABLE);
			dropTable(db, "offline_table");
			dropTable(db, "province_table");
			onCreate(db);
			StoFilePreference.getInstance(context).setServerIp(
					"http://pda.sto.cn:2227/receive/");
			StoFilePreference.getInstance(context).setServerIps(
					"http://pda.sto.cn:2226/app_software/klandroid/STO.apk");
		}
	}

	private void dropTable(SQLiteDatabase db, String tableName) {
		String sql = "DROP TABLE IF EXISTS " + tableName;
		db.execSQL(sql);
	}

	/**
	 * 修改数据库，增加时间 年月日
	 */
	private void alterTable(SQLiteDatabase db) {
		StoTableFieldName fieldName = new StoTableFieldName();
		for (EnumTableNames tableName : OperateEnumTable.getAllTables()) {
			String sql = "alter table " + tableName.getTableName() + " add "
					+ fieldName.SCAN_DATE2 + " char(10)";
			db.execSQL(sql);
			sql = "alter table " + tableName.getTableName() + " add "
					+ fieldName.USER_DEFINED6 + " char(40)";
			db.execSQL(sql);
			sql = "alter table " + tableName.getTableName() + " add "
					+ fieldName.USER_DEFINED7 + " char(50)";
			db.execSQL(sql);
			sql = "alter table " + tableName.getTableName() + " add "
					+ fieldName.USER_DEFINED8 + " char(200)";
			db.execSQL(sql);

		}
	}

	private void createIndex(SQLiteDatabase db) {

		for (EnumTableNames tableName : OperateEnumTable.getAllTables()) {
			// 创建数据库的索引
			String CREATE_INDEX_BY_SCAN_DATE = "CREATE INDEX "
					+ " IF NOT EXISTS " + tableName.getTableName() + "_"
					+ fieldName.SCAN_DATE + "_idx ON "
					+ tableName.getTableName() + " (" + fieldName.SCAN_DATE
					+ ")";

			String CREATE_INDEX_BY_SEND_STATE = "CREATE INDEX "
					+ " IF NOT EXISTS " + tableName.getTableName() + "_"
					+ fieldName.SEND_STATE + "_idx ON "
					+ tableName.getTableName() + " (" + fieldName.SEND_STATE
					+ ")";

			db.execSQL(CREATE_INDEX_BY_SCAN_DATE);
			db.execSQL(CREATE_INDEX_BY_SEND_STATE);

		}
	}

	/**
	 * 创建数据库
	 * 
	 * @param db
	 */
	private void CreateTable(SQLiteDatabase db) {
		StoTableFieldName fieldName = new StoTableFieldName();
		for (EnumTableNames tableName : EnumTableNames.values()) {
			String CREATE_TABLE = "CREATE TABLE " + " if not exists "
					+ tableName.getTableName() + "("
					// 命令类型
					+ fieldName.COMMAND_TYPE + " char(6)" + ","
					// PDA_UUID
					+ fieldName.PDA_UUID + " char(32)" + ","
					// 新增/删除
					+ fieldName.AUXOPCODE + " char(8) " + ","
					// 角色
					+ fieldName.ROLE + " char(8)" + ","

					// 单号类型
					+ fieldName.EXPTYPE + " char(8)" + ","
					// 上下站编号、目的地 /收件员、派件员编号(14)
					+ fieldName.NEXT_STATION + " char(14)" + ","

					+ fieldName.SCAN_DATE_AND_TIME + " char(30)" + ","
					// 袋号(14)
					+ fieldName.BAG_NUMBER + " char(14)" + ","
					// 包号(14)
					+ fieldName.PACK_NUMBER + " char(14)" + ","
					// 扫描日期(10)
					+ fieldName.SCAN_DATE + " char(10)" + ","
					// 扫描日期(10)
					+ fieldName.SCAN_DATE2 + " char(10)" + ","
					// 扫描时间(10)
					+ fieldName.SCAN_TIME + " char(10)" + ","
					// 物品类型/功能类别(8)
					+ fieldName.FUNCTION_TYPE + " char(8)" + ","
					// 快件类型(8)
					+ fieldName.EXPRESS_TYPE + " char(8)" + ","
					// 运单编码/包条码/车签条码(30)
					+ fieldName.BAR_CODE_NUMBER + " char(16)" + ","
					// 扫描员编号（15）
					+ fieldName.SCANNER_NUMBER + " char(15)" + ","
					// 班次（1）
					+ fieldName.SHIFT + " text" + ","
					// 异常签收原因编号/路由号(10)
					+ fieldName.REASON_NUMBER + " char(30)" + ","
					// 代收款、车牌号(8)
					+ fieldName.CAR_NUMBER + " char(8)" + ","
					// 货单标识/车牌ID条码(10)
					+ fieldName.CARID_NUMBER + " char(10)" + ","
					// 备注/收件人手机号/签收人/同行名称(14)
					+ fieldName.REMARK_NAME + " char(14)" + ","
					// 重量(8)
					+ fieldName.WEIGHT + " char(8)" + ","
					// 输入重量(8)
					+ fieldName.INPUT_WEIGHT + " char(8)" + ","
					// 寄件人手机号(15)
					+ fieldName.SEND_PHONE + " char(15)" + ","
					// 巴枪唯一码(15)
					+ fieldName.UNIQUE_CODE + " char(15)" + ","
					// 件数（8）
					+ fieldName.NUMBER + " char(8)" + ","
					// 运费（10）
					+ fieldName.SHIPPING + " char(10)" + ","
					// 到付款（8）
					+ fieldName.TO_PAYMENT + " char(8)" + ","
					// 支付方式(1)
					+ fieldName.PAYMENT + " char(8)" + ","
					// 收件人(20)
					+ fieldName.RECV_PERSON + " char(20)" + ","
					// 寄件人(20)
					+ fieldName.SEND_PERSON + " char(20)" + ","
					// 上传时间(19)
					+ fieldName.UPLOAD_TIME + " char(19)" + ","
					// 网点编号(10)
					+ fieldName.NET_CODE + " char(10)" + ","
					// 工号(10)
					+ fieldName.JOB_NUMBER + " char(10)" + ","
					// 用户定义3(40)
					+ fieldName.USER_DEFINED3 + " char(40)" + ","
					// 用户定义4(50)
					+ fieldName.USER_DEFINED4 + " char(50)" + ","
					// 用户定义5(200)
					+ fieldName.USER_DEFINED5 + " char(200)" + ","
					// 发送状态(2)
					+ fieldName.SEND_STATE + " char(2)" + ","
					// 元数据标示(2)
					+ fieldName.METADATA + " char(2)" + ","
					// 不同的标志(20)
					+ fieldName.DIFFERENT_FLAG + " char(20)"
					// + " , primary key" + "(" + fieldName.BAR_CODE_NUMBER +
					// ","
					// + fieldName.SCAN_DATE + "," + fieldName.DIFFERENT_FLAG
					// + ")"
					+ ")";
			// 创建数据库的索引
			String CREATE_INDEX = "CREATE INDEX if not exists "
					+ tableName.getTableName() + "_idx ON "
					+ tableName.getTableName() + " ("
					+ fieldName.BAR_CODE_NUMBER + "," + fieldName.SCAN_DATE
					+ "," + fieldName.SEND_STATE + ")";
			db.execSQL(CREATE_TABLE);
			db.execSQL(CREATE_INDEX);

		}
	}

	/**
	 * 创建签收信息表
	 * 
	 * @param db
	 */
	private void CreateSignInfoTable(SQLiteDatabase db) {
		StoSingInfoTable table = new StoSingInfoTable();
		String CREATE_TABLE = "CREATE TABLE " + " if not exists "
				+ table.SIGN_INFO_TABLE_NAME + "("
				// 代号
				+ table.SIGN_INFO_KEY + " char(32)" + ","
				// 名称
				+ table.SIGN_INFO_VALUE + " char(32),"
				// 是否用户增加
				+ table.SIGN_INFO_IS_USER + " char(1),"
				// 备用字段
				+ table.SIGN_INFO_UNDEFIND1 + " char(8))";
		db.execSQL(CREATE_TABLE);
		table.InitData(db);
	}

	/**
	 * get database operation style
	 * 
	 * @param type
	 *            tag
	 * @return SQLiteDatabase tag
	 */

	public SQLiteDatabase getSqliteDatabase(final int type) {
		if (type == UnifiedStaticType.WRITE_DATABASE && writeDB == null)
			writeDB = getWritableDatabase();
		else if (type == UnifiedStaticType.READ_DATABASE && readDB == null)
			readDB = getReadableDatabase();

		if (type == UnifiedStaticType.WRITE_DATABASE)
			return writeDB;
		else
			return readDB;
	}

	/**
	 * 关闭数据库
	 */
	public void closeDb(int type) {
		if (writeDB != null && type == UnifiedStaticType.WRITE_DATABASE) {
			writeDB.close();
			writeDB = null;
		} else if (readDB != null && type == UnifiedStaticType.READ_DATABASE) {
			readDB.close();
			readDB = null;
		}
	}

	/**
	 * 关闭游标
	 * 
	 * @param c
	 *            游标
	 */
	public void closeCursor(Cursor c) {
		if (c != null) {
			c.close();
		}
	}

	/**
	 * 根据SQL取得游标
	 * 
	 * @param sql
	 *            搜索SQL
	 * @param args
	 *            参数
	 * @return <Cursor>游标
	 */
	public Cursor getCursor(int dataBase, String sql, String[] args) {
		return getSqliteDatabase(dataBase).rawQuery(sql, args);
	}

	/**
	 * 插入凯立售后数据
	 * 
	 * @param db
	 */
	private void createKailiAftermarketTable(SQLiteDatabase db) {
		db.execSQL(CREATE_KAILI_AFTERMARKET_TABLE);
		ProcessAftermarket aftermarket = new ProcessAftermarket();
		aftermarket.insertAftermarketData(db);
	}

	@Override
	public SQLiteDatabase getWritableDatabase() {
		// TODO Auto-generated method stub
		return super.getWritableDatabase();
	}

}
